package thesis.database;

import java.sql.Connection;
import java.sql.Date;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

public class ServiceJDBCTemplate implements ServiceDAO {
	private DataSource dataSource;
	private JdbcTemplate jdbcTemplateObject;

	@Override
	public void setDataSource(DataSource ds) {
		this.dataSource = ds;
		this.jdbcTemplateObject = new JdbcTemplate(dataSource);

	}

	@Override
	public int create(Service service) {
		final Service data = service;
		final String sql = "insert into use_service(customer_id,taxi_id,distance,accept_time,begin_longitude,begin_latitude,"
				+ "end_longitude,end_latitude,begin_time,end_time,begin_address,end_address,price,status) values "
				+ "(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplateObject.update(new PreparedStatementCreator() {
			public java.sql.PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				java.sql.PreparedStatement ps = connection.prepareStatement(
						sql, new String[] { "id" });
				ps.setInt(1, data.getCustomerId());
				ps.setInt(2, data.getTaxiId());
				ps.setDouble(3, data.getDistance());
				ps.setTimestamp(4, new Timestamp(data.getAcceptTime().getTime()));
				ps.setString(5, data.getBeginLongitude());
				ps.setString(6, data.getBeginLatitude());
				ps.setString(7, data.getEndLongitude());
				ps.setString(8, data.getEndLatitude());
				ps.setTimestamp(9, new Timestamp(data.getBeginTime().getTime()));
				ps.setTimestamp(10, new Timestamp(data.getEndTime().getTime()));
				ps.setString(11, data.getBeginAddress());
				ps.setString(12, data.getEndAddress());
				ps.setDouble(13, data.getPrice());
				ps.setInt(14, data.getStatus());

				return ps;
			}
		}, keyHolder);
		System.out.println("Created Service Record");

		return keyHolder.getKey().intValue();

	}

	@Override
	public int createReturnId(Service service) {

		return create(service);
	}

	@Override
	public Service getService(Integer id) {
		String sql = "select * from use_service where id = ?";
		Service service = jdbcTemplateObject.queryForObject(sql,
				new Object[] { id }, new ServiceMapper());
		return service;
	}

	@Override
	public List<Service> listServices() {
		String sql = "select * from use_service";
		List<Service> list = jdbcTemplateObject.query(sql, new ServiceMapper());
		return list;
	}

	@Override
	public void delete(Integer id) {
		String sql = "delete from use_service where id = ?";
		jdbcTemplateObject.update(sql, id);
		System.out.println("Deleted Use_service Record with ID = " + id);

	}

	@Override
	public void update(Service service) {
		String sql = "update use_service set customer_id = ?,taxi_id = ?,distance = ?,accept_time = ?,begin_longitude = ?,begin_latitude = ?,"
				+ "end_longitude = ?,end_latitude = ?,begin_time = ?,end_time = ?,begin_address = ?,end_address = ?,price = ?,status = ? where id = ? ";
		jdbcTemplateObject.update(sql, service.getCustomerId(),
				service.getTaxiId(), service.getDistance(),
				service.getAcceptTime(), service.getBeginLongitude(),
				service.getBeginLatitude(), service.getEndLongitude(),
				service.getEndLatitude(), service.getBeginTime(),
				service.getEndTime(), service.getBeginAddress(),
				service.getEndAddress(), service.getPrice(),
				service.getStatus(), service.getId());
		System.out.println("Updated Service Record with ID = "
				+ service.getId());

	}

	@Override
	public Service getServiceByPhone(String phone) {
		Service service = null;
		String sql = "select * from use_service where customer_id = (select customer_id from customer where home_phone = ? or office_phone = ? or newest_phone = ?) order by accept_time desc limit 1";
		service = jdbcTemplateObject.queryForObject(sql, new Object[] { phone,
				phone, phone }, new ServiceMapper());

		return service;
	}

}
